﻿using DataUp.Model;
using FreeSql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;


namespace DataUp
{
    public class SqlContext
    {
        private string _connectionStr;
        private DataType _dbType;
        public IFreeSql DB { get; set; }

        public SqlContext(string connectionStr, DataType dbType)
        {
            Init(connectionStr, dbType);
        }

        public SqlContext()
        {
        }

        /// <summary>
        /// 初始化数据库上下文
        /// </summary>
        /// <param name="connectionStr">数据库连接</param>
        /// <param name="dbType">数据库类型</param>
        public void Init(string connectionStr, DataType dbType)
        {
            _connectionStr = connectionStr;
            _dbType = dbType;
            DB = new FreeSqlBuilder()
                .UseConnectionString(_dbType, _connectionStr)
                .UseAutoSyncStructure(false) //自动同步实体结构到数据库
                .UseGenerateCommandParameterWithLambda(true)
                .UseMonitorCommand((evt) =>
                {
                    //string sql = evt.CommandText;
                    //Console.WriteLine(sql);
                })
                .Build(); //请务必定义成 Singleton 单例模式
        }

        #region 执行Sql
        /// <summary>
        /// 执行Sql返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, object Parameters = null)
        {
            DbParameter[] Parameter = null;
            if (Parameters != null)
            {
                Parameter = Parameter.ChangeSqlParameter(_dbType).ToArray();
            }
            return DB.Ado.ExecuteNonQuery(CommandType.Text, sql, Parameter);
        }

        /// <summary>
        /// 执行Sql返回受影响的行数(异步)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public async Task<int> ExecuteNonQueryAsync(string sql, object Parameters = null)
        {
            DbParameter[] Parameter = null;
            if (Parameters != null)
            {
                Parameter = Parameter.ChangeSqlParameter(_dbType).ToArray();
            }
            return await DB.Ado.ExecuteNonQueryAsync(CommandType.Text, sql, Parameter).ConfigureAwait(false);
        }

        /// <summary>
        /// 执行SQL查询，返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, object Parameters = null)
        {
            DbParameter[] Parameter = null;
            if (Parameters != null)
            {
                Parameter = Parameter.ChangeSqlParameter(_dbType).ToArray();
            }
            return DB.Ado.ExecuteDataTable(CommandType.Text, sql, Parameter);
        }

        /// <summary>
        /// 执行SQL查询，返回DataTable(异步)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public async Task<DataTable> ExecuteDataTableAsync(string sql, object Parameters = null)
        {
            DbParameter[] Parameter = null;
            if (Parameters != null)
            {
                Parameter = Parameter.ChangeSqlParameter(_dbType).ToArray();
            }
            return await DB.Ado.ExecuteDataTableAsync(CommandType.Text, sql, Parameter).ConfigureAwait(false);
        }

        #endregion

        #region 查询
        /// <summary>
        /// 查询实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public T QueryInfo<T>(Expression<Func<T, bool>> whereException = null, string orderBy = null) where T : class
        {
            var select = DB.Select<T>().WhereIf(whereException != null, whereException);
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                select.OrderBy(orderBy);
            }
            return select.First();
        }

        /// <summary>
        /// 查询实体类(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public async Task<T> QueryInfoAsync<T>(Expression<Func<T, bool>> whereException = null, string orderBy = null) where T : class
        {
            var select = DB.Select<T>().WhereIf(whereException != null, whereException);
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                select.OrderBy(orderBy);
            }
            return await select.FirstAsync().ConfigureAwait(false);
        }

        /// <summary>
        /// 查询集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public List<T> QueryList<T>(Expression<Func<T, bool>> whereException = null, string orderBy = null) where T : class
        {
            var select = DB.Select<T>().WhereIf(whereException != null, whereException);
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                select.OrderBy(orderBy);
            }
            return select.ToList();
        }

        /// <summary>
        /// 查询集合(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public async Task<List<T>> QueryListAsync<T>(Expression<Func<T, bool>> whereException = null, string orderBy = null) where T : class
        {
            var select = DB.Select<T>().WhereIf(whereException != null, whereException);
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                select.OrderBy(orderBy);
            }
            return await select.ToListAsync().ConfigureAwait(false);
        }

        /// <summary>
        /// 查询分页列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <param name="orderBy"></param>
        /// <param name="page">当前页数</param>
        /// <param name="limit">每页数量</param>
        /// <returns></returns>
        public PageModel<T> QueryPageList<T>(Expression<Func<T, bool>> whereException = null, string orderBy = null, int page = 1, int limit = 10) where T : class
        {
            var select = DB.Select<T>().WhereIf(whereException != null, whereException);
            long Count = 0;
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                select.OrderBy(orderBy);
            }
            var list = select.Page(page, limit).Count(out Count).ToList();
            //计算总页数
            int pageCount = (Math.Ceiling(Count.ObjToDecimal() / limit.ObjToDecimal())).ObjToInt();
            return new PageModel<T>
            {
                count = Count,
                data = list,
                PageSize = limit,
                PageCount = pageCount,
                PageIndex = page
            };
        }

        /// <summary>
        /// 查询分页列表(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <param name="orderBy"></param>
        /// <param name="page">当前页数</param>
        /// <param name="limit">每页数量</param>
        /// <returns></returns>
        public async Task<PageModel<T>> QueryPageListAsync<T>(Expression<Func<T, bool>> whereException = null, string orderBy = null, int page = 1, int limit = 10) where T : class
        {
            var select = DB.Select<T>().WhereIf(whereException != null, whereException);
            long Count = 0;
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                select.OrderBy(orderBy);
            }
            var list = await select.Page(page, limit).Count(out Count).ToListAsync().ConfigureAwait(false);
            //计算总页数
            int pageCount = (Math.Ceiling(Count.ObjToDecimal() / limit.ObjToDecimal())).ObjToInt();
            return new PageModel<T>
            {
                count = Count,
                data = list,
                PageSize = limit,
                PageCount = pageCount,
                PageIndex = page
            };
        }

        /// <summary>
        /// 查询行数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <returns></returns>
        public long QueryCount<T>(Expression<Func<T, bool>> whereException = null) where T : class
        {
            return DB.Select<T>().WhereIf(whereException != null, whereException).Count();
        }

        /// <summary>
        /// 查询行数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <returns></returns>
        public async Task<long> QueryCountAsync<T>(Expression<Func<T, bool>> whereException = null) where T : class
        {
            return await DB.Select<T>().WhereIf(whereException != null, whereException).CountAsync().ConfigureAwait(false);
        }

        #endregion

        #region 修改


        /// <summary>
        /// 修改数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Setobject"></param>
        /// <param name="whereException"></param>
        /// <returns></returns>
        public int Update<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> whereException) where T : class
        {
            return DB.Update<T>().Set(columns).Where(whereException).ExecuteAffrows();
        }

        /// <summary>
        /// 修改数据(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Setobject"></param>
        /// <param name="whereException"></param>
        /// <returns></returns>
        public async Task<int> UpdateAsync<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> whereException) where T : class
        {
            return await DB.Update<T>().Set(columns).Where(whereException).ExecuteAffrowsAsync().ConfigureAwait(false);
        }

        #endregion

        #region 新增
        /// <summary>
        /// 新增一条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <returns></returns>
        public long Add<T>(T item) where T : class
        {
            return DB.Insert<T>(item).ExecuteIdentity();
        }

        /// <summary>
        /// 新增一条记录(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <returns></returns>
        public async Task<long> AddAsync<T>(T item) where T : class
        {
            return await DB.Insert<T>(item).ExecuteIdentityAsync().ConfigureAwait(false);
        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <returns></returns>
        public long Add<T>(IEnumerable<T> item) where T : class
        {
            return DB.Insert(item).ExecuteAffrows();
        }

        /// <summary>
        /// 批量新增(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <returns></returns>
        public async Task<long> AddASync<T>(IEnumerable<T> item) where T : class
        {
            return await DB.Insert(item).ExecuteAffrowsAsync().ConfigureAwait(false);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <returns></returns>
        public long Delete<T>(Expression<Func<T, bool>> whereException = null) where T : class
        {
            return DB.Delete<T>().Where(whereException).ExecuteAffrows();
        }

        /// <summary>
        /// 删除一条记录(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereException"></param>
        /// <returns></returns>
        public async Task<long> DeleteAsync<T>(Expression<Func<T, bool>> whereException = null) where T : class
        {
            return await DB.Delete<T>().Where(whereException).ExecuteAffrowsAsync().ConfigureAwait(false);
        }
        #endregion


    }
}
